﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace QuanLyThongTinNhanKhau.model.DAO
{
    class SQLServiceDAO
    {
        private static String _connectString = @"Data Source=ADMIN-PC\SQLEXPRESS;Initial Catalog=DOANTOTNGHIEP-Manh;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";
        protected SqlConnection cn;
        protected SqlCommand command;
        protected SqlDataAdapter adapter;

        public SQLServiceDAO()
        {
            cn = new SqlConnection(_connectString);
        }

        public static String CONNECTSTRING
        {
            get { return SQLServiceDAO._connectString; }
            set { SQLServiceDAO._connectString = value; }
        }

        public void connect()
        {
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
        }

        public void disconnect()
        {
            if (cn.State == ConnectionState.Open)
            {
                cn.Close();
            }
        }

        public DataTable getDataTable(String sql)
        {
            DataTable dt;
            try
            {
                this.connect();
                dt = new DataTable();
                adapter = new SqlDataAdapter(sql, cn);
                adapter.Fill(dt);
                this.disconnect();

            }
            finally
            {
                this.disconnect();
            }
            return dt;
        }

        public Boolean executeNonQuery(String sql)
        {
            Boolean check = true;
            try
            {
                this.connect();
                command = new SqlCommand(sql, cn);
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                check = false;
                throw;
            }
            finally
            {
                this.disconnect();
            }
            return check;
            
        }

        public Object executeScalar(String sql)
        {
            this.connect();
            command = new SqlCommand(sql, cn);
            object o = command.ExecuteScalar();
            this.disconnect();
            return o;
        }

        public int excuteSP(String StoreName, SqlParameter[] param)
        {
            int count = 0;
            command = new SqlCommand(StoreName, cn);
            command.CommandType = CommandType.StoredProcedure;
            if (param != null)
            {
                foreach (SqlParameter i in param)
                {
                    command.Parameters.Add(i);
                }
            }

            try
            {
                this.connect();
                count = command.ExecuteNonQuery();
            }
            finally
            {
                this.disconnect();
            }
            return count;
        }

        public DataTable getDataSP(String StoreName, SqlParameter[] param)
        {
            this.connect();
            command = new SqlCommand(StoreName,cn);
            command.CommandType = CommandType.StoredProcedure;
            if (param != null)
            {
                foreach (SqlParameter i in param)
                {
                    command.Parameters.Add(i);
                }
            }
            adapter = new SqlDataAdapter(command);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            this.disconnect();
            return dt;
        }

        public Object executeScalarSP(String StoreName,SqlParameter[] param)
        {
            this.connect();
            command = new SqlCommand(StoreName, cn);
            command.CommandType = CommandType.StoredProcedure;
            if (param != null)
            {
                foreach (SqlParameter i in param)
                {
                    command.Parameters.Add(i);
                }
            }

            Object o = command.ExecuteScalar();
            this.disconnect();
            return o;
        }
    }
}
